-- phpMyAdmin SQL Dump
-- version 4.8.5
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Nov 04, 2020 at 08:50 AM
-- Server version: 10.1.40-MariaDB
-- PHP Version: 7.1.29

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `muzejsi2020grupa2`
--

-- --------------------------------------------------------

--
-- Table structure for table `period`
--

CREATE TABLE `period` (
  `id perioda` smallint(6) NOT NULL,
  `naziv` varchar(50) NOT NULL,
  `pocetak` varchar(30) DEFAULT NULL,
  `zavrsetak` varchar(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `postavka`
--

CREATE TABLE `postavka` (
  `id postavke` int(10) UNSIGNED NOT NULL,
  `naziv` varchar(100) NOT NULL,
  `datum od` date NOT NULL,
  `datum do` date DEFAULT NULL,
  `opis` text
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `postavka u prostoriji`
--

CREATE TABLE `postavka u prostoriji` (
  `id postavke` int(10) UNSIGNED NOT NULL,
  `oznaka prostorije` char(5) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `predmet`
--

CREATE TABLE `predmet` (
  `inventarni broj` int(11) NOT NULL,
  `broj u knjizi ulaza` varchar(20) NOT NULL,
  `naziv` varchar(100) NOT NULL,
  `datum nabavke` date NOT NULL,
  `visina` smallint(6) DEFAULT NULL,
  `sirina` smallint(6) DEFAULT NULL,
  `dubina` smallint(6) DEFAULT NULL,
  `period` smallint(6) DEFAULT NULL,
  `vrsta predmeta` tinyint(3) UNSIGNED NOT NULL,
  `fotografija` varchar(250) DEFAULT NULL,
  `opis` text
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `predmet u postavci`
--

CREATE TABLE `predmet u postavci` (
  `inventarni broj` int(11) NOT NULL,
  `id postavke` int(10) UNSIGNED NOT NULL,
  `aktuelna` tinyint(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `prostorija`
--

CREATE TABLE `prostorija` (
  `oznaka prostorije` char(5) NOT NULL,
  `naziv` int(50) DEFAULT NULL,
  `velicina` int(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `vrsta predmeta`
--

CREATE TABLE `vrsta predmeta` (
  `id vrste predmeta` tinyint(3) UNSIGNED NOT NULL,
  `naziv` varchar(50) NOT NULL,
  `napomena` varchar(250) DEFAULT NULL,
  `kategorija` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `period`
--
ALTER TABLE `period`
  ADD PRIMARY KEY (`id perioda`),
  ADD UNIQUE KEY `naziv` (`naziv`);

--
-- Indexes for table `postavka`
--
ALTER TABLE `postavka`
  ADD PRIMARY KEY (`id postavke`),
  ADD UNIQUE KEY `naziv` (`naziv`);

--
-- Indexes for table `postavka u prostoriji`
--
ALTER TABLE `postavka u prostoriji`
  ADD PRIMARY KEY (`id postavke`,`oznaka prostorije`),
  ADD KEY `FKProstorija` (`oznaka prostorije`);

--
-- Indexes for table `predmet`
--
ALTER TABLE `predmet`
  ADD PRIMARY KEY (`inventarni broj`),
  ADD UNIQUE KEY `broj u knjizi ulaza` (`broj u knjizi ulaza`),
  ADD KEY `naziv` (`naziv`,`period`,`vrsta predmeta`),
  ADD KEY `period` (`period`),
  ADD KEY `vrsta predmeta` (`vrsta predmeta`);

--
-- Indexes for table `predmet u postavci`
--
ALTER TABLE `predmet u postavci`
  ADD PRIMARY KEY (`inventarni broj`,`id postavke`),
  ADD KEY `fkPREDEMTPOSTAVKA` (`id postavke`);

--
-- Indexes for table `prostorija`
--
ALTER TABLE `prostorija`
  ADD PRIMARY KEY (`oznaka prostorije`),
  ADD UNIQUE KEY `naziv` (`naziv`);

--
-- Indexes for table `vrsta predmeta`
--
ALTER TABLE `vrsta predmeta`
  ADD PRIMARY KEY (`id vrste predmeta`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `vrsta predmeta`
--
ALTER TABLE `vrsta predmeta`
  MODIFY `id vrste predmeta` tinyint(3) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `postavka u prostoriji`
--
ALTER TABLE `postavka u prostoriji`
  ADD CONSTRAINT `FKProstorija` FOREIGN KEY (`oznaka prostorije`) REFERENCES `prostorija` (`oznaka prostorije`) ON UPDATE CASCADE ON DELETE CASCADE,
  ADD CONSTRAINT `fkPOSTAVKAPROSTORIJA` FOREIGN KEY (`id postavke`) REFERENCES `postavka` (`id postavke`) ON UPDATE CASCADE ON DELETE CASCADE;

--
-- Constraints for table `predmet`
--
ALTER TABLE `predmet`
  ADD CONSTRAINT `predmet_ibfk_1` FOREIGN KEY (`period`) REFERENCES `period` (`id perioda`) ON UPDATE CASCADE ON DELETE RESTRICT,
  ADD CONSTRAINT `predmet_ibfk_2` FOREIGN KEY (`vrsta predmeta`) REFERENCES `vrsta predmeta` (`id vrste predmeta`) ON UPDATE CASCADE ON DELETE RESTRICT;

--
-- Constraints for table `predmet u postavci`
--
ALTER TABLE `predmet u postavci`
  ADD CONSTRAINT `fkPREDEMTPOSTAVKA` FOREIGN KEY (`id postavke`) REFERENCES `postavka` (`id postavke`) ON UPDATE CASCADE ON DELETE CASCADE,
  ADD CONSTRAINT `predmet u postavci_ibfk_1` FOREIGN KEY (`inventarni broj`) REFERENCES `predmet` (`inventarni broj`) ON UPDATE CASCADE ON DELETE CASCADE,
  ADD CONSTRAINT `predmet u postavci_ibfk_2` FOREIGN KEY (`id postavke`) REFERENCES `postavka` (`id postavke`) ON UPDATE CASCADE ON DELETE CASCADE;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;